Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Public Class BLL
    Public Shared Function POSRunQuery(ByVal querystr As String) As DataTable
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand(querystr, PublicVariable.Connection)
                MyCommand.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataTable = New DataTable()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                PublicVariable.Connection.Close()
                If mytable Is Nothing Then Return Nothing
                Return mytable
            Else
                Return Nothing
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return Nothing
        End Try
    End Function
    Public Shared Function ObjectGetAll(ByVal sp_Name As String) As DataTable
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand(sp_Name, PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataTable = New DataTable()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                PublicVariable.Connection.Close()
                Return mytable
            Else
                Return Nothing
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return Nothing
        End Try
    End Function
    Public Shared Function ObjectGetBy(ByVal sp_Name As String, _
                                        ByVal pVarName As ArrayList, _
                                        ByVal pValList As ArrayList) As DataTable
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand(sp_Name, PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                For i As Integer = 0 To pVarName.Count - 1
                    MyCommand.Parameters.AddWithValue(pVarName(i), pValList(i))
                Next
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataTable = New DataTable()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                PublicVariable.Connection.Close()
                Return mytable
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Function
    Public Shared Function Object_Update_Result(ByVal sp_Name As String, _
                                        ByVal pVarName As ArrayList, _
                                        ByVal pValList As ArrayList) As Decimal
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand(sp_Name, PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                For i As Integer = 0 To pVarName.Count - 1
                    MyCommand.Parameters.AddWithValue(pVarName(i), pValList(i))
                Next
                Dim myParameter As SqlParameter = New SqlParameter("@Result", SqlDbType.Decimal)
                myParameter.Direction = ParameterDirection.Output
                MyCommand.Parameters.Add(myParameter)
                MyCommand.ExecuteNonQuery()
                Dim re As Integer
                re = Convert.ToInt32(MyCommand.Parameters(MyCommand.Parameters.Count - 1).Value)
                Return re
                PublicVariable.Connection.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Function

    Public Shared Sub Object_Update_NonResult(ByVal sp_Name As String, _
                                        ByVal pVarName As ArrayList, _
                                        ByVal pValList As ArrayList)
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand(sp_Name, PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                For i As Integer = 0 To pVarName.Count - 1
                    MyCommand.Parameters.AddWithValue(pVarName(i), pValList(i))
                Next
                MyCommand.ExecuteNonQuery()
                PublicVariable.Connection.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Shared Function ReturnXML(ByVal TableName As String, ByVal dtbl As DataTable) As Boolean
        If dtbl.Rows.Count = 0 Then Return False
        Dim DirPath As String = ""
        Dim FileName As String

        DirPath = Application.StartupPath() + "\" + PublicVariable.POSStoreCode
        If Not Directory.Exists(DirPath) Then
            Directory.CreateDirectory(DirPath)
        End If
        FileName = DirPath + "\" + TableName + "_" + dtbl.Rows(0).Item("KeyName").ToString + ".xml"
        If File.Exists(FileName) Then
            File.Delete(FileName)
        End If
        dtbl.TableName = TableName
        dtbl.WriteXml(FileName)
        Return True
    End Function
End Class
